***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


***locals 
local F=100  
local yu=200  // this is the chosen range +/- corra for yields we will use


**************************************************
*** Combine all raw data files and prepare data
**************************************************

*BILLS (secondary)
use  "`dir'/milena_fake_APR2020_allbill", clear

*BONDS (secondary)
append using "`dir'/milena_fake_APR2020_2016H1_bond"
append using "`dir'/milena_fake_APR2020_2016H2_bond"
append using "`dir'/milena_fake_APR2020_2017H1_bond"
append using "`dir'/milena_fake_APR2020_2017H2_bond"
append using "`dir'/milena_fake_APR2020_2018H1_bond"
append using "`dir'/milena_fake_APR2020_2018H2_bond"
append using "`dir'/milena_fake_APR2020_2019H1_bond"
append using "`dir'/milena_fake_APR2020_2019H2_bond"
drop year
count if market_type==""

*RBB (secondary)
append using "`dir'/milena_fake_APR2020_RBB"

*BILLS AND BONDS (auction)
append using "`dir'/auctions_Aug2020_corrected" 

replace market_type="auction" if market_type=="" // this works because the market-type is secondary for all other
rename bidderid bidderid_auc

replace Maturity = maturity 		if market_type=="auction"
replace settlement_date = issuedate if market_type=="auction"
drop maturity 
drop issuedate

*Fill in time 
replace execution_time_stamp = TenderTimestamp  	 if TenderTimestamp!=. 			  & market_type=="auction"
replace execution_time_stamp = NetPositionTimestamp  if execution_time_stamp ==.      & market_type=="auction"
browse execution_time_stamp 

*Rename and label
replace auctiontype="TBREG"  if auctiontype=="CTB"
rename ISIN 				isin
rename fake_trading_venue 	IIROC_TRADING_VENUE_ID // need to put the correct names as in original dataset
rename fake_lei 			LEI 				   // need to put the correct names as in original dataset
rename execution_time_stamp exec_time
rename iiroc_date   		date	
rename quantity 			IIROC_QUANTITY
rename price 				IIROC_PRICE 
rename yield 				IIROC_YIELD
rename IIROC_SIDE_TYPE_CODE IIROC_SIDE
rename Term_current 		Term
label var Term "Term of the auction (only filled in for auction data)"

*Combine some variables
gen bidderid 	 = bidderid_auc 		if market_type=="auction"
replace bidderid = IIROC_DEALER_NAME    if market_type=="secondary" 
drop bidderid_auc IIROC_DEALER_NAME

*Normalize price, quantity, yields
local F   = 100								 // face value
local mio = 1000000 						 // used to normalize quantities 

replace IIROC_PRICE = IIROC_PRICE * `F'/100  //use new face value of 1,000 CAD

foreach var in IIROC_QUANTITY volume_vdate volume_tdate outstanding totalallottedamtmax BidAmtSUM BidAllottedAmtSUM issuesum biddueamtsum allotment_buyback switch_amt_repurchased switch_amt_total NetPositionAmt   {
replace `var' = `var'/`mio' 				 // normalize quantities
}

replace IIROC_YIELD		= IIROC_YIELD*100    // in basis points
replace yieldAtCurve 	= yieldAtCurve*100
replace corra 			= corra*100
replace BidYield 		= BidYield *100 

*Drop CDS data for now (bring back below)
rename volume_vdate volume_vdate_old
rename volume_tdate volume_tdate_old 

*Drop missing observatiosn
drop if AuctionDate==. & market_type=="auction"

*Info on gleif_related: 
	*gen gleif_related = "Y" if _merge==3 & (iiroc_date>=relation_start) & (iiroc_date<relation_end)
	*tab gleif_related relatedParty


**************************************************	
********** COMBINE WITH PUBLIC AUCTIONS DATA
**************************************************
do "`dropbox_data_create'/public_auctions.do"
count if market_type=="auction" | market_type=="secondary" // Number of observations in raw data

**************************************************	
********** CLEANING 
**************************************************
do "`dropbox_data_create'/cleaning.do"

**************************************************	
********** CREATE VARIABLES 
**************************************************
do "`dropbox_data_create'/create_variables.do"

**************************************************
********** MERGE WITH BLOOMBERG AND CANDEAL DATA
**************************************************
do "`dropbox_data_create'/bloomberg_candeal.do"

**************************************************
********** DOUBLE COUNTING
**************************************************
do "`dropbox_data_create'/double_counting.do"

**************************************************
********** CREATE SOME VARIABLES
**************************************************

*matuirty category per month
gen year = year(date)
gen mat_type=. // 31 days
replace mat_type =1 if 0<=days_to_maturity &  days_to_maturity<=31
foreach i of numlist 2/600 {
replace mat_type= `i'  if  (`i' -1)*31 <days_to_maturity &  days_to_maturity<=`i'*31
}
label var mat_type "months to maturity"
	
*matuirty category per year	
gen mat_type_year = . // 365 days	
replace mat_type_year =1 if 0<=days_to_maturity &  days_to_maturity<=365
foreach i of numlist 2/30 {
replace mat_type_year= `i'  if  (`i' -1)*365 <days_to_maturity &  days_to_maturity<=`i'*365
}
replace mat_type_year=31 if days_to_maturity> 30 * 365
label var mat_type_year "years to maturity, where 31 is >30 years"
	
*inventory 
gen 	quantity 	= - IIROC_QUANTITY if IIROC_SIDE=="BUY"
replace quantity 	=  IIROC_QUANTITY  if IIROC_SIDE=="SELL"
bys bidderid isin (exec_time_grid): gen inventory_et = sum(quantity)

*clean up
drop year 
gen year = year(date)
drop isin_id 
egen isin_id=group(isin)
drop hour

**************************************************
********** CONVERT INTEREST RATES INTO SAME FORMULA
**************************************************
*Transaction yields 
do "`dropbox_data_create'/data_yas_v2.do"
*Bloomberg yields 
do "`dropbox_data_create'/data_yas_bl_v2.do"
*Candeal yields 
do "`dropbox_data_create'/data_yas_can.do"

*Clean false or empty retailers
replace trading_venue="OTC" if retailer==1 & counter_type=="CLIENT" // retailers can only trade OTC
drop if retailer==. & market_type=="secondary" & counter_type=="CLIENT" // drop empty retailers


**************************************************
********** SAVE
**************************************************
save "`local_data'/data_base_bonds_bills.dta", replace 








